# # create database bookstore
# # default character set gb2313
# # collate gb2312_chinese_ci;
#
# # alter database bookstore
# # default character set utf8mb4
# # default collate utf8mb4_0900_ai_ci
#
# # show databases
#
# # use bookstore;
# # alter table book
# # add 浏览次数 tinyint null,
# # drop column 书名;
#
# # usw bookstor;
# # alter table book
# # rename to mybook;
#
# # use bookstore;
# # rename table mybook to booklist, members to memberlist;
#
# create table book_copy1 like book;
#
# create table book_copy2
# as
# (select *from book)
#
# use bookstore;
# show tables;
#
# desribe book;
#
# desc book 图书编号;
#
# alter table book
# add primary key(图书编号),
# add unique u_idx(书名);
#
# alter table book
# drop primary key,
# drop index u_idx;
#
# create table book_ref
# (
#     图书编号 varchar(20) null,
#     书名 varchar(20) not null,
#     出版日期 date null,
#     primary key (书名),
#     foreign key (图书编号)
#     references book (图书编号)
#     on delete restrict
# on update restrict
# );
#
# select * from book_ref
# where 图书编号 not in
# (select 图书编号 from book);
#
# alter table sell
# add foreign key(用户号)
# references members
# on delete cascade
# on update cascade;
#
# create database petstore;
#
# use petstore;
# create table account(
#     userid char(6) not null,
# fullname varchar(10) not null,
# password varchar(20) not null,
# sex char(2) not null,
# address varchar(40) null,
# email varchar(20) null,
# phone varchar(11) not null,
# primary key (userid)
# );
# create table category(
# catid char(10) not null,
# catname varchar(20) null,
# primary key (catid)
# );
#
# #第四单元
# use bookstore ;
# insert into book values(
#     'TP.9501','计算机','dreamweather',
#     ''
# );
#
# update book
# set 数量 = 数量+10
# update members
# set 联系电话 =’13802551234‘，密码='111111'
# where 姓名='张三'
#
# update sell,book
# set sell.订购册数=订购册数-2，book.数量=数量+2
# where sell.图书编号=book.图书编号 and sell.订单号='6';
#
# use bookstore;
# delete from members
# where 姓名='张三'
#
# use bookstore;
# delete from book
# where 数量<=5;
#
# #第五单元
# select * from members;
#
# select 书名 as name,作者 as auther, 出版社 as publisher
# from book
# where 图书类别='计算机';
#
# selecet 图书编号，书名
# case
# when 数量 is null then '尚未进货'
# when 数量 <5 then '需进货'
# when 数量 >=5 and 数量<=5 then '库存正常'
# end as 库存
# from book;
#
# select 图书编号,round(订购册数*订购单价，2)as 订购金额
# from sell
# where 是否发货='已发货';
#
# select*
# from book
# where 书名='网页程序设计';
#
# select*
# from book
# where 单价>30
#
# select 订单号,订购时间,是否收货
# from sell
# where 是否收货<=>null
#
# select*
# from sell
# where 是否收货='已收货' and 是否结清='已结清';
#
# select 用户号，姓名，注册时间
# from members
# where 姓名 like '李%'
#
# select 图书编号，书名 from book
# where 图书编号 like'%6_'
#
# select 图书编号，书名
# from book
# where 书名 like'%#_%'escape '#';
#
# select* from sell
# where 是否发货 is null
#
# select*
# from members as users
#
# select book.书名, sell.订购册数,sell.订购时间
# from book,sell
# where book.图书编号=sell.图书编号;
#
# select*
# from sell
# where 用户号 in
# (select 用户号 from members where 姓名=‘张三’)
#
# select 订单号，用户号，图书编号，订购册数 from sell where 用户号='c0138';
#
# select count(是否收货) as '已收货的订单数' from sell
#
# select count(订购册数) as '订购册数在5本以上的订单数'
# from sell where 订单册数>5
